package dao.impl;

import dao.LendDao;
import entity.Book;
import entity.Record;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import util.DataSourceUtil;
import util.Util;

import java.sql.SQLException;
import java.util.List;

public class LendDaoImpl implements LendDao {
    public boolean userHasLend(String userId, String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select count(*) from records where userId=? and bookId=?";
        Object[] params={userId,bookId};
        Long count=runner.query(sql,new ScalarHandler<Long>(),params);
        if(count>0){
            return true;
        }else{
            return false;
        }
    }

    public List<Record> queryAllLend() throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from records";
        return runner.query(sql,new BeanListHandler<Record>(Record.class));
    }

    public boolean userIsNotReturn(String userId, String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select count(*) from records where userId=? and bookId=? and returnDate=?";
        Object[] params={userId,bookId,"null"};
        Long count=runner.query(sql,new ScalarHandler<Long>(),params);
        if(count>0){
            return true;
        }else{
            return false;
        }
    }

    public boolean addRecord(String userId, String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="insert into records values(?,?,?,?)";
        Object[] params={bookId,userId, Util.getDate(),"null"};
        int result=runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public boolean deleteRecord(String userId, String bookId,String borrowDate) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="delete from records where userId=? and bookId=? and borrowDate=?";
        Object[] params={userId,bookId,borrowDate};
        int result=runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public boolean updateRecord(String userId, String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="update records set returnDate=? where userId=? and bookId=? and returnDate=?";
        Object[] params={Util.getDate(),userId,bookId, "null"};
        int result=runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public List<Record> borrowInformation(String userId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from records where userId=?";
        return runner.query(sql,new BeanListHandler<Record>(Record.class),userId);
    }


    public List<Record> borrowCount(String userId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select userId,bookId,count(*) borrowCount from records where userId=? group by bookId";
        return runner.query(sql,new BeanListHandler<Record>(Record.class),userId);
    }

    public int getTotalCount(String sql,Object[] params) throws SQLException {
        return Util.getCountTotal(sql,params).intValue();
    }

    public List<Record> queryRecordByPage(int currentPage,int pageSize) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from records limit ?,?";
        Object[] params={currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Record>(Record.class),params);
    }

    public List<Record> queryRecordByPageRequire(int currentPage,int pageSize,Object param) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select distinct * from records where bookId=? or userId=? limit ?,?";
        Object[] params={param,param,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Record>(Record.class),params);
    }

    public List<Record> queryCountByPageRequire(int currentPage,int pageSize,Object param) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select  bookId,count(*) borrowCount from records where bookId=? group by bookId limit ?,?";
        Object[] params={param,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Record>(Record.class),params);
    }
    public List<Record> queryCountByPage(int currentPage,int pageSize) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select  bookId,count(*) borrowCount from records  group by bookId limit ?,?";
        Object[] params={currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Record>(Record.class),params);
    }

    public List<Record> queryRecordByUserPage(int currentPage,int pageSize,String userId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from records where userId=? limit ?,?";
        Object[] params={userId,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Record>(Record.class),params);
    }

    public List<Record> queryRecordByUserPageRequire(int currentPage,int pageSize,String userId,Object param) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from records,books where books.bookId=records.bookId and records.userId=? and (books.bookId=? or books.bookName=?)  limit ?,?";
        Object[] params={userId,param,param,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Record>(Record.class),params);
    }


}
